package deweb.cimaplanning.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.cpi.cornpp.costelement.exception.CostElementException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

import deweb.cimaplanning.misc;
import deweb.cimaplanning.dto.PODistributionListPerPlant;

public class PODistributionListPerPlantDao extends DBDAO {
	/** Datasource name */
	private static final String dsName = misc.DS_NAME;//$NON-NLS-1$
	
	/**SQL Select all cost elements*/
	protected static final String SQL_SELECT_ALL_ELEMENTS = "SELECT purchase_order_version_id, plant_id, version_id FROM po_distribution_list_to_plant"; //$NON-NLS-1$


	public PODistributionListPerPlantDao() {
	}
	
	/**
	 * Get all the cost elements
	 * @return
	 * @throws CostElementException
	 */
	public ArrayList getArray(int orderField, boolean ascendingOrder, String filters) throws Exception {
		StringBuffer sql = null;
		PODistributionListPerPlant dto = null;
		ArrayList array	= null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			array = new ArrayList(20);
			sql = new StringBuffer(SQL_SELECT_ALL_ELEMENTS);
			
			if (filters.length() > 0){
				sql.append(SQL_WHERE);
				sql.append(filters);
			}
			
			sql.append(SQL_ORDER_BY);
			switch (orderField){
				case 1: sql.append(PODistributionListPerPlant.FIELD_1);
						break;
				case 2: sql.append(PODistributionListPerPlant.FIELD_2);
					break;
				case 3: sql.append(PODistributionListPerPlant.FIELD_3);
					break;
			}
			
			if (ascendingOrder){
				sql.append(SQL_ASCENDING_ORDER);
			} else {
				sql.append(SQL_DESCENDING_ORDER);
			}
			
			pstm = prepareSQL(sql.toString(), dsName);
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new PODistributionListPerPlant();
				
				dto.setPlantId(rs.getInt("plant_id"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
				dto.setVersionId(rs.getInt("version_id"));
								
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}	
		
		return array;		
	}

}
